package approve.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import approve.model.DepartmentLang;

import model.pojo.Employee;
import models.util.MySqlDataAccessHelper;

public class NotApproveDAO {

	public static ArrayList<DepartmentLang> getListDepartment(String lang,String db){
		ArrayList<DepartmentLang> lst = new ArrayList<DepartmentLang>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = "SELECT IDDepartment,DepartmentName FROM department_lang where LangID='"+lang+"' ORDER BY DepartmentName ASC";
		try {
			mysql.open(db);
			ResultSet rs = mysql.executeQuery(sql);
			while(rs.next()){
				lst.add( new DepartmentLang(rs.getInt("IDDepartment"),rs.getString("DepartmentName")));
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}
	
	public static ArrayList<DepartmentLang> getListDepartment1(String choosedepartment,String lang,String db){
		ArrayList<DepartmentLang> lst = new ArrayList<DepartmentLang>();
		lst.add(new DepartmentLang(0,"---"+choosedepartment+"---"));
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = "SELECT IDDepartment,DepartmentName FROM department_lang where LangID='"+lang+"' ORDER BY DepartmentName ASC";
		try {
			mysql.open(db);
			ResultSet rs = mysql.executeQuery(sql);
			while(rs.next()){
				lst.add( new DepartmentLang(rs.getInt("IDDepartment"),rs.getString("DepartmentName")));
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}
	
	public static ArrayList<Employee> getListEmployeeAddRefer(int depart_id,String db,List<Employee> listrefer){
		ArrayList<Employee> lst = new ArrayList<Employee>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = "SELECT IDEmployee,FullName FROM employee WHERE IDDepartment =" +depart_id + " and State = 1";
		try {
			mysql.open(db);
			ResultSet rs = mysql.executeQuery(sql);
			if(listrefer!=null && listrefer.size()>0){
				while(rs.next()){	
					int kt=1;
					for(int i=0;i<listrefer.size();i++){
						if(listrefer.get(i).getIdemployee().equals(rs.getString("IDEmployee"))){
							kt=0;
							break;
						}
					}
					if(kt==1){
						lst.add(new Employee(rs.getString("IDEmployee"), rs.getString("FullName"),false));
					}
				}
			}else{
				while(rs.next()){
					lst.add(new Employee(rs.getString("IDEmployee"), rs.getString("FullName"),false));
				}
			}
			
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}
	
	public static ArrayList<Employee> getListEmployeeSubRefer(int depart_id,String db,String strid){
		ArrayList<Employee> lst = new ArrayList<Employee>();
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql = "SELECT IDEmployee,FullName FROM employee WHERE IDDepartment =" +depart_id + " and State = 1 and IDEmployee NOT IN ("+strid+")";
		if(strid.equals("")){
			sql = "SELECT IDEmployee,FullName FROM employee WHERE IDDepartment =" +depart_id + " and State = 1";
		}
		try {
			mysql.open(db);
			ResultSet rs = mysql.executeQuery(sql);
			while(rs.next()){
				lst.add(new Employee(rs.getString("IDEmployee"), rs.getString("FullName"),false));
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}
}